package manager;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.primefaces.model.SortOrder;

import constants.Globals;

import exception.IBToolsException;

import structures.HistoryData;
import structures.ValuePair;
import util.DateHelper;
import util.HibernateUtil;

@SuppressWarnings("unchecked")
public class LoadNativeTableManager {
	
	private static final String historyQuery =  
			" SELECT *" +
					" FROM (SELECT row_.*, ROWNUM rownum_" +
							" FROM (  SELECT " +
									" this_.CALL_DATETIME AS DATETIME," +
											" this_.ENVIRONMENT AS ENVIRONMENT," +
											" this_.ERROR_TYPE AS ERROR_TYPE," +
											" this_.SERVICE_NAME AS SERVICE_NAME," +
											" this_.SUCCESS AS SUCCESS," +
											" this_.USER_CODE AS USER_CODE," +
											" CD.XML_STRING," +
											" CE.ERROR_TEXT," +
											" this_.OID AS OID" +
										" FROM T32556.IBTOOLS_LOG_SERVICE_CALL this_" +
										" left join T32556.IBTOOLS_LOG_SERVICE_CALL_OUT cd" +
										" on THIS_.OID = CD.LOG_SERVICE_CALL" +
										" and CD.PART = 1" +
										" left join T32556.IBTOOLS_LOG_SERVICE_CALL_ERROR ce" +
										" on THIS_.OID = CE.LOG_SERVICE_CALL" +
										" and CE.PART = 1" +
										" WHERE this_.USER_CODE = '%userCode%'" ;
										

	private static final String historyQueryServiceName =  " AND this_.SERVICE_NAME LIKE '%serviceName%'";
	
	private static final String historyQueryDate =  " AND this_.CALL_DATETIMESTAMP >= to_date(%date1%,'ddmmyyyy')" +
													" AND this_.CALL_DATETIMESTAMP <= to_date(%date2%,'ddmmyyyy')";
	
	private static final String historyQueryPost =  " ORDER BY this_.CALL_DATETIMESTAMP DESC) row_" +
														" WHERE ROWNUM <= (%bigRowNum%))" +
															" WHERE rownum_ > (%smallRowNum%)" ;
	

	public static List<ValuePair> loadPossibleValuePairs(String tableName, String valueColumn, String labelColumn) throws IBToolsException{

    	Session session = HibernateUtil.getDEVSession();
    	Transaction tx = null;
    	List<String> list = null;
    	try {
    		tx = HibernateUtil.beginDEVTransaction(session);
    		String orderColumn = valueColumn;
    		if(!labelColumn.equalsIgnoreCase(Globals.EMPTY_STRING)){
    			valueColumn = valueColumn + ", " + labelColumn;
    		}
    		String query = "select " + valueColumn + " from " + tableName + " where rownum < 30 order by " + orderColumn + " asc";
			list = session.createSQLQuery(query)
					.list();
			tx.commit();
		} catch (HibernateException e) {
			System.out.println("---Why????---");
			System.out.println(e.getLocalizedMessage());
			if(tx != null)
				tx.rollback();
		} catch(Exception e){
			System.out.println(e.getLocalizedMessage());
			
		}
    	return stringArrayToValuePair(list);
	}

	public static List<ValuePair> loadTableNames(int rowNum, String substring) throws IBToolsException{

    	Session session = HibernateUtil.getDEVSession();
    	Transaction tx = null;
    	List<String> list = null;
    	try {
    		tx = HibernateUtil.beginDEVTransaction(session);
    		String query = "";
    		substring = substring.replaceAll("\\*", "%").toUpperCase();
    		if(substring.indexOf(".") > -1){
    			String owner = substring.substring(0, substring.indexOf("."));
    			String tableSubstring = substring.substring(substring.indexOf(".")+1, substring.length());
	    		query = "select OWNER || '.' || TABLE_NAME, OWNER || '.' || TABLE_NAME from SYS.ALL_ALL_TABLES where rownum < " + rowNum + " and " +
	    				" OWNER = '" + owner + "'";
	    		if( !tableSubstring.equalsIgnoreCase("")){
		    		query += " and TABLE_NAME like '" + tableSubstring + "%' order by TABLE_NAME desc";
	    		}
    		}
    		else {
	    		query = "select OWNER || '.' || TABLE_NAME, OWNER || '.' || TABLE_NAME from SYS.ALL_ALL_TABLES where rownum < " + rowNum + " and " +
	    				" OWNER like '" + substring + "%' order by OWNER desc";
    		}
			list = session.createSQLQuery(query).list();
			tx.commit();
		} catch (HibernateException e) {
			System.out.println("---Why????---");
			System.out.println(e.getLocalizedMessage());
			if(tx != null)
				tx.rollback();
		} catch(Exception e){
			System.out.println(e.getLocalizedMessage());
			
		}
    	return stringArrayToValuePair(list);
	}
	public static ValuePair analyzeCodeDescriptionColumns(String table) throws IBToolsException{
		ValuePair columnPair = new ValuePair(Globals.EMPTY_STRING, Globals.EMPTY_STRING);
		if(table != null && !table.equalsIgnoreCase(Globals.EMPTY_STRING) && table.indexOf(".") > -1){
			Session session = HibernateUtil.getDEVSession();
	    	Transaction tx = null;
	    	List<String> list = null;
			String[] tableInfo = table.split("\\.");
			String tableName = tableInfo[1];
	    	
	    	String query = "select COLUMN_NAME from SYS.ALL_TAB_COLUMNS where TABLE_NAME = '" + tableName + "'";
	    	
	    	try {
	    		tx = HibernateUtil.beginDEVTransaction(session);
				list = session.createSQLQuery(query).list();
				tx.commit();
			} catch (HibernateException e) {
				System.out.println("---Why????---");
				System.out.println(e.getLocalizedMessage());
				if(tx != null)
					tx.rollback();
			}
	    	int labelLevel = 0;
	    	int valueLevel = 0;
	    	if(list != null){
	    		for (String columnName : list) {
					if(labelLevel < 2){
						if("SCREENNAME".indexOf(columnName) > -1){
							columnPair.setLabel(columnName);
							labelLevel = 2;
						}
					}
					if(labelLevel < 1){
						if("TITLE,NAME,DESCRIPTION,ACIKLAMA,ADI".indexOf(columnName) > -1 ||
								"ACIKLAMA".indexOf(columnName) > -1 ||
								"TITLE".indexOf(columnName) > -1 ||
								"NAME".indexOf(columnName) > -1 ||
								"DESCRIPTION".indexOf(columnName) > -1 ||
								"ADI".indexOf(columnName) > -1 ||
								columnName.indexOf("ACIKLAMA") > -1 ||
								columnName.indexOf("TITLE") > -1 ||
								columnName.indexOf("NAME") > -1 ||
								columnName.indexOf("DESCRIPTION") > -1 ||
								columnName.indexOf("ADI") > -1){
							columnPair.setLabel(columnName);
							labelLevel = 1;
						}
					}
					if(valueLevel < 2){
						if("CODE".indexOf(columnName) > -1 ||
								"KOD".indexOf(columnName) > -1){
							columnPair.setValue(columnName);
							valueLevel = 2;
						}
					}
					if(valueLevel < 1){
						if(columnName.indexOf("CODE") > -1 ||
								columnName.indexOf("KOD") > -1){
							columnPair.setValue(columnName);
							valueLevel = 1;
						}
					}
				}
	    	}
		}
		return columnPair;
	}
	
	public static List<ValuePair> loadColumnNames(int rowNum, String table, String columnSubstring) throws IBToolsException{
		List<ValuePair> response = null;
		if(table != null && !table.equalsIgnoreCase(Globals.EMPTY_STRING) && table.indexOf(".") > -1){
			Session session = HibernateUtil.getDEVSession();
	    	Transaction tx = null;
	    	List<String> list = null;
    		columnSubstring = columnSubstring.replaceAll("\\*", "%").toUpperCase();
    		
    		String query = "";
			String[] tableInfo = table.split("\\.");
			String[] columnInfo = null;
			String owner = tableInfo[0];
			String tableName = tableInfo[1];
			String valueColumn = " '' || ";
			
    		if(columnSubstring.indexOf(",") > -1){
				columnInfo = columnSubstring.split(",");
				valueColumn = " '" + columnInfo[0] + "' || ',' || ";
				if(columnInfo.length == 1){
					columnSubstring = Globals.EMPTY_STRING;
				}
				else {
					columnSubstring = columnInfo[1];
				}
    		}
    		query = "select " + valueColumn + " COLUMN_NAME, " + valueColumn + " COLUMN_NAME from SYS.ALL_TAB_COLUMNS where rownum < " + rowNum;
    		if( !tableName.equals(Globals.EMPTY_STRING)){
    			query += " and TABLE_NAME = '" + tableName +"' and OWNER = '" + owner +"' ";
    		}
    		query += " and COLUMN_NAME like '" + columnSubstring + "%' order by COLUMN_NAME desc";

	    	try {
	    		tx = HibernateUtil.beginDEVTransaction(session);
				list = session.createSQLQuery(query).list();
				tx.commit();
			} catch (HibernateException e) {
				System.out.println("---Why????---");
				System.out.println(e.getLocalizedMessage());
				if(tx != null)
					tx.rollback();
			}
			response = stringArrayToValuePair(list);
		}
    	return response;
	}
	
	private static List<ValuePair> stringArrayToValuePair(List<String> list){
		List<ValuePair> pairs = new ArrayList<ValuePair>();
		if(list != null){
			for (Object listObject : list) {
				if(listObject instanceof Object[]){
					Object[] objectLineArray = (Object[]) listObject;
					String label = (objectLineArray[0] == null ? "" : objectLineArray[0].toString());
					String value = (objectLineArray[1] == null ? "" : objectLineArray[1].toString());
					pairs.add(new ValuePair(label, value));
				}
				else {
					pairs.add(new ValuePair(listObject.toString(), "Undescribed"));
				}
			}
		}
		return pairs;
	}
	
	public static List<HistoryData> loadHistoryNative(int start, int pageSize, String sortField, SortOrder sortOrder, String historyServiceNameFilter,
			String historyCallDateFilter, String userCode, int isSuccessful) throws IBToolsException{

    	Session session = HibernateUtil.getDEVSession();
    	Transaction tx = null;
    	List<String> list = null;
    	try {
    		
    		tx = HibernateUtil.beginDEVTransaction(session);
    		StringBuffer historyFullString = new StringBuffer(historyQuery.replaceAll("%userCode%", SessionManager.getLoginUserCode()));
    		if( !historyServiceNameFilter.equalsIgnoreCase(Globals.EMPTY_STRING)){
	    		String preLike = historyServiceNameFilter.startsWith(".") ? "" : "%";
	    		String postLike = historyServiceNameFilter.endsWith(".") ? "" : "%";
	    		historyServiceNameFilter = historyServiceNameFilter.replaceAll("\\.", "");
	    		historyFullString.append(historyQueryServiceName.replaceAll("%serviceName%", preLike + historyServiceNameFilter.toUpperCase() + postLike));
    		}
    		if(!historyCallDateFilter.equalsIgnoreCase(Globals.EMPTY_STRING)){
    			SimpleDateFormat d = new SimpleDateFormat("yyyyMMdd");
    			Date toSearch = d.parse(historyCallDateFilter);
    			String fromDate = DateHelper.getDateStringWithoutTime(toSearch);
    			String toDate = DateHelper.getDateStringWithoutTime(DateHelper.getTomorrowDate(toSearch));
	    		historyFullString.append(historyQueryDate.replaceAll("%date1%", fromDate).replaceAll("%date2%", toDate));
    		}
    		historyFullString.append(historyQueryPost.replaceAll("%smallRowNum%", String.valueOf(start)).replaceAll("%bigRowNum%", String.valueOf(start + pageSize)));
			list = session.createSQLQuery(historyFullString.toString()).list();
			tx.commit();
		} catch (HibernateException e) {
			System.out.println("---Why????---");
			System.out.println(e.getLocalizedMessage());
			if(tx != null)
				tx.rollback();
		} catch(Exception e){
			System.out.println(e.getLocalizedMessage());
			
		}
    	return stringArrayToHistoryData(list);
	}
	
	private static List<HistoryData> stringArrayToHistoryData(List<String> list){
		List<HistoryData> historyDatas = new ArrayList<HistoryData>();
		if(list != null){
			for (Object listObject : list) {
				Object[] objectLineArray = (Object[]) listObject;
				historyDatas.add(new HistoryData(objectLineArray));
			}
		}
		return historyDatas;
	}

	public static String loadQueryOid(String queryName) throws IBToolsException{

    	Session session = HibernateUtil.getDEVSession();
    	Transaction tx = null;
    	List<String> list = null;
    	try {
    		
    		tx = HibernateUtil.beginDEVTransaction(session);
    		list = session.createSQLQuery("select to_char(oid) oid from gen.dad_queries where name = '" + queryName + "'").list();
			tx.commit();
		} catch (HibernateException e) {
			System.out.println("---Why????---");
			System.out.println(e.getLocalizedMessage());
			if(tx != null)
				tx.rollback();
		} catch(Exception e){
			System.out.println(e.getLocalizedMessage());
			
		}
    	return list.get(0).toString();
	}
}
